﻿namespace Atomic.Data.AdoNet
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections.Generic;

    /// <summary>
    /// 数据提供程序扩展。
    /// </summary>
    public static class DataProviderExtensions
    {
        /// <summary>
        /// 是否存在数据。
        /// </summary>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="tableName">表名。</param>
        /// <param name="field">字段名。</param>
        /// <param name="where">条件。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>存在数据返回 true，不存在则返回 false。</returns>
        public static bool Exist(this IDataProvider dataProvider, string tableName, string field, string where, params IDataParameter[] commandParameters)
        {
            if (string.IsNullOrEmpty(tableName))
                throw new ArgumentNullException("tbName");

            if (string.IsNullOrEmpty(field))
                throw new ArgumentNullException("field");

            if (!string.IsNullOrWhiteSpace(where))
                where = " WHERE " + where;

            string sqlText = "SELECT " + field + " FROM " + tableName + where;

            object val = dataProvider.Single(sqlText, commandParameters);

            if (val != null && val != DBNull.Value)
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// 分页的形式查询数据。
        /// </summary>
        /// <typeparam name="TModelType">返回的实体类型。</typeparam>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="pageIndex">页码。</param>
        /// <param name="pageSize">页大小。</param>
        /// <param name="tableName">表名或者视图。</param>
        /// <param name="primary">主键。</param>
        /// <param name="fields">查询输出的字段。</param>
        /// <param name="where">搜索条件。</param>
        /// <param name="order">排序方式。</param>
        /// <param name="top">获得满足条件的前 top 条数据。</param>
        /// <param name="action">获得实体的方法。</param>
        /// <returns>实体集合。</returns>
        public static IEnumerable<TModelType> GetPage<TModelType>(
            this IDataProvider dataProvider,
            int pageIndex,
            int pageSize,
            string tableName,
            string primary,
            string fields,
            string where,
            string order,
            int top,
            Func<IDataReader, TModelType> action)
        {
            if (dataProvider is SQLServerDataProvider)
            {
                IDataParameter[] parameters =
                {
                    new SqlParameter("@pageIndex",SqlDbType.Int),
                    new SqlParameter("@pageSize",SqlDbType.Int),
                    new SqlParameter("@tableName",SqlDbType.NVarChar, 50),
                    new SqlParameter("@primary",SqlDbType.NVarChar, 50),
                    new SqlParameter("@fields",SqlDbType.NVarChar, 500),
                    new SqlParameter("@sqlWhere",SqlDbType.NVarChar, 2000),
                    new SqlParameter("@orderby",SqlDbType.NVarChar, 8),
                    new SqlParameter("@top",SqlDbType.Int)
                };

                parameters[0].Value = pageIndex < 1 ? 1 : pageIndex;
                parameters[1].Value = pageSize < 1 ? 10 : pageSize;
                parameters[2].Value = tableName;
                parameters[3].Value = string.IsNullOrWhiteSpace(primary) ? "*" : primary;
                parameters[4].Value = fields;
                parameters[5].Value = where;
                parameters[6].Value = string.IsNullOrWhiteSpace(order) ? "DESC" : order;
                parameters[7].Value = top;

                return dataProvider.SqlQuery<TModelType>(CommandType.StoredProcedure, "P_ItemPage", action, parameters);
            }

            return null;
        }

        /// <summary>
        /// 分页的形式查询数据。
        /// </summary>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="pageIndex">页码。</param>
        /// <param name="pageSize">页大小。</param>
        /// <param name="tableName">表名或者视图。</param>
        /// <param name="primary">主键。</param>
        /// <param name="fields">查询输出的字段。</param>
        /// <param name="where">搜索条件。</param>
        /// <param name="order">排序方式。</param>
        /// <param name="top">获得满足条件的前 top 条数据。</param>
        /// <returns>数据读取器。</returns>
        public static IDataReader GetPage(
            this IDataProvider dataProvider,
            int pageIndex,
            int pageSize,
            string tableName,
            string primary,
            string fields,
            string where,
            string order,
            int top)
        {
            if (dataProvider is SQLServerDataProvider)
            {
                IDataParameter[] parameters =
                {
                    new SqlParameter("@pageIndex",SqlDbType.Int),
                    new SqlParameter("@pageSize",SqlDbType.Int),
                    new SqlParameter("@tableName",SqlDbType.NVarChar, 50),
                    new SqlParameter("@primary",SqlDbType.NVarChar, 50),
                    new SqlParameter("@fields",SqlDbType.NVarChar, 500),
                    new SqlParameter("@sqlWhere",SqlDbType.NVarChar, 2000),
                    new SqlParameter("@orderby",SqlDbType.NVarChar, 8),
                    new SqlParameter("@top",SqlDbType.Int)
                };

                parameters[0].Value = pageIndex < 1 ? 1 : pageIndex;
                parameters[1].Value = pageSize < 1 ? 10 : pageSize;
                parameters[2].Value = tableName;
                parameters[3].Value = string.IsNullOrWhiteSpace(primary) ? "*" : primary;
                parameters[4].Value = fields;
                parameters[5].Value = where;
                parameters[6].Value = string.IsNullOrWhiteSpace(order) ? "DESC" : order;
                parameters[7].Value = top;

                return dataProvider.SqlQuery(CommandType.StoredProcedure, "P_ItemPage", parameters);
            }

            return null;
        }

        /// <summary>
        /// 获得总记录数。
        /// </summary>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="table">表名。</param>
        /// <param name="where">条件。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>总记录数。</returns>
        public static int GetCount(this IDataProvider dataProvider, string table, string where, params IDataParameter[] commandParameters)
        {
            string sql = string.Format("SELECT COUNT(1) FROM {0}{1}", table, string.IsNullOrWhiteSpace(where) ? "" : " WHERE " + where);
            return dataProvider.Single<int>(sql, commandParameters);
        }

        /// <summary>
        /// 查询数据。
        /// </summary>
        /// <typeparam name="TModelType">返回的实体类型。</typeparam>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="action">获得实体的方法。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>实体集合。</returns>
        public static IEnumerable<TModelType> SqlQuery<TModelType>(
            this IDataProvider dataProvider, string sql, Func<IDataReader, TModelType> action, params IDataParameter[] commandParameters)
        {
            return dataProvider.SqlQuery<TModelType>(CommandType.Text, sql, action, commandParameters);
        }

        /// <summary>
        /// 查询数据。
        /// </summary>
        /// <typeparam name="TModelType">返回的实体类型。</typeparam>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="action">获得实体的方法。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>实体集合。</returns>
        public static IEnumerable<TModelType> SqlQuery<TModelType>(
            this IDataProvider dataProvider, CommandType commandType, string sql, Func<IDataReader, TModelType> action, params IDataParameter[] commandParameters)
        {
            IList<TModelType> items = new List<TModelType>();

            using (IDataReader dataReader = dataProvider.SqlQuery(commandType, sql, commandParameters))
            {
                while (dataReader.Read())
                {
                    var item = action(dataReader);

                    if (item != null)
                    {
                        items.Add(item);
                    }
                }
            }

            return items;
        }

        /// <summary>
        /// 查询数据。
        /// </summary>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>数据读取器。</returns>
        public static IDataReader SqlQuery(this IDataProvider dataProvider, string sql, params IDataParameter[] commandParameters)
        {
            return dataProvider.SqlQuery(CommandType.Text, sql, commandParameters);
        }

        /// <summary>
        /// 查询单条数据。
        /// </summary>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>查询结果。</returns>
        public static object Single(this IDataProvider dataProvider, string sql, params IDataParameter[] commandParameters)
        {
            return dataProvider.Single(CommandType.Text, sql, commandParameters);
        }

        /// <summary>
        /// 查询单条数据。
        /// </summary>
        /// <typeparam name="TDataType">返回结果数据的类型。</typeparam>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>查询结果。</returns>
        public static TDataType Single<TDataType>(this IDataProvider dataProvider, string sql, params IDataParameter[] commandParameters)
        {
            var data = dataProvider.Single(CommandType.Text, sql, commandParameters);

            if (data != null && data != DBNull.Value)
            {
                return (TDataType)data;
            }

            return default(TDataType);
        }

        /// <summary>
        /// 执行 SQL 命令。
        /// </summary>
        /// <param name="dataProvider">数据提供程序。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>影响行数。</returns>
        public static int ExecuteSqlCommand(this IDataProvider dataProvider, string sql, params IDataParameter[] commandParameters)
        {
            return dataProvider.ExecuteSqlCommand(CommandType.Text, sql, commandParameters);
        }
    }
}